package com.mccullough.utility;

import java.sql.*;

public class ConnectionFactory {
	private static ConnectionFactory instance;
	private String connectionString;

	public static ConnectionFactory getInstance() throws Exception {
		if (instance == null)
			instance = new ConnectionFactory();
		return instance;
	}

	private ConnectionFactory() throws Exception {
		Class.forName("org.hsqldb.jdbcDriver");
		setConnectionString("jdbc:hsqldb:mem:owasp");
	}

	public Connection getConnection() throws SQLException {
		return DriverManager.getConnection(getConnectionString());
	}

	public String getConnectionString() {
		return connectionString;
	}

	public void setConnectionString(String connectionString) {
		this.connectionString = connectionString;
	}

	public void resetDatabase() throws Exception {
		Connection con;

		con = ConnectionFactory.getInstance().getConnection();
		Statement cmd = con.createStatement();

		try {
			cmd.execute("drop table gsUser");
		} catch (Exception ex) {
		}

		cmd.execute("CREATE TABLE GsUser(id int NOT NULL,"
				+ "userName varchar(50) NULL,password varchar(50) NULL,"
				+ "userType varchar(50) NULL)");

		cmd.execute("INSERT INTO GsUser" + "(id,userName,password,userType)"
				+ "VALUES(1,'Jerry','comic','seller')");

		cmd.execute("INSERT INTO GsUser" + "(id,userName,password,userType)"
				+ "VALUES(2,'Cosmo','kramer','seller')");

		cmd.execute("INSERT INTO GsUser" + "(id,userName,password,userType)"
				+ "VALUES(3,'Elaine','witchy','buyer')");

		cmd.execute("INSERT INTO GsUser" + "(id,userName,password,userType)"
				+ "VALUES(4,'George','bosco','buyer')");

		try {
			cmd.execute("drop table Item");
		} catch (Exception ex) {
		}

		cmd.execute("CREATE TABLE Item(	id int NOT NULL,"
				+ "userId int NOT NULL,Description varchar(1024) NULL,"
				+ "Price real NULL)");

		cmd.execute("INSERT INTO Item (Id,UserId,Description,Price) VALUES "
				+ "(1,1,'Puffy Shirt - Look like a <b>pirate</b>.',18.00)");

		cmd.execute("INSERT INTO Item (Id,UserId,Description,Price) VALUES "
				+ "(2,1,'Stinky car',18000.00)");

		cmd.execute("INSERT INTO Item (Id,UserId,Description,Price) VALUES "
				+ "(3,2,'Coffee table book',16.00)");

		cmd.execute("INSERT INTO Item (Id,UserId,Description,Price) VALUES "
				+ "(4,2,'Calvin Klein Underwear',8.00)");

	}
}
